if exists (select 1 from sysobjects where name = 'get_dienstschema' and type = 'P')
begin
   drop procedure get_dienstschema
   print 'Procedure: get_dienstschema deleted ...'
end
go
create procedure get_dienstschema(
  @schemaid     int = 1,
  @abteilungid  int = 1
)
as
begin
  set nocount on

  if @abteilungid = 0 select @abteilungid = null

  select dienstid = ds.DienstID, 
         gerechnet = ds.Gerechnet, 
         einsatzprotag = ds.EinsatzProTag, 
         dienstname = d.Name, 
         kurztext = b.Kurztext,
         langtext = b.Langtext,
         abteilungid = a.AbteilungID,
         abteilungname = a.Name
  from DienstSchema ds 
  left outer join Dienst d
    on ds.DienstID = d.DienstID
  left outer join Beschreibung b
    on b.BeschrID = d.BeschrID
  left outer join Abteilung a
    on a.AbteilungID = ds.AbteilungID
 where ds.SchemaID = @schemaid 
   and (ds.AbteilungID = @abteilungid or @abteilungid is null)

end
go
print 'Procedure: get_dienstschema done ...'
go

grant exec on get_dienstschema to prsadmins with grant option
go
grant exec on get_dienstschema to prsusers
go

